{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style type='text/css'>\n",
       ".datatable table.frame { margin-bottom: 0; }\n",
       ".datatable table.frame thead { border-bottom: none; }\n",
       ".datatable table.frame tr.coltypes td {  color: #FFFFFF;  line-height: 6px;  padding: 0 0.5em;}\n",
       ".datatable .bool    { background: #DDDD99; }\n",
       ".datatable .object  { background: #565656; }\n",
       ".datatable .int     { background: #5D9E5D; }\n",
       ".datatable .float   { background: #4040CC; }\n",
       ".datatable .str     { background: #CC4040; }\n",
       ".datatable .row_index {  background: var(--jp-border-color3);  border-right: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  font-size: 9px;}\n",
       ".datatable .frame tr.coltypes .row_index {  background: var(--jp-border-color0);}\n",
       ".datatable th:nth-child(2) { padding-left: 12px; }\n",
       ".datatable .hellipsis {  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .vellipsis {  background: var(--jp-layout-color0);  color: var(--jp-cell-editor-border-color);}\n",
       ".datatable .na {  color: var(--jp-cell-editor-border-color);  font-size: 80%;}\n",
       ".datatable .footer { font-size: 9px; }\n",
       ".datatable .frame_dimensions {  background: var(--jp-border-color3);  border-top: 1px solid var(--jp-border-color0);  color: var(--jp-ui-font-color3);  display: inline-block;  opacity: 0.6;  padding: 1px 10px 1px 5px;}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import warnings\n",
    "warnings.simplefilter('ignore')\n",
    "\n",
    "import re\n",
    "import gc\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "pd.set_option('max_columns', None)\n",
    "pd.set_option('max_rows', 1000)\n",
    "\n",
    "from tqdm.notebook import tqdm\n",
    "\n",
    "%matplotlib inline\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "from sklearn.feature_extraction.text import TfidfVectorizer\n",
    "from sklearn.decomposition import TruncatedSVD\n",
    "from sklearn.preprocessing import LabelEncoder\n",
    "from sklearn.model_selection import KFold\n",
    "from sklearn.metrics import mean_squared_error\n",
    "from gensim.models import Word2Vec\n",
    "\n",
    "import lightgbm as lgb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(59288, 26)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数据ID</th>\n",
       "      <th>容纳人数</th>\n",
       "      <th>便利设施</th>\n",
       "      <th>洗手间数量</th>\n",
       "      <th>床的数量</th>\n",
       "      <th>床的类型</th>\n",
       "      <th>卧室数量</th>\n",
       "      <th>取消条款</th>\n",
       "      <th>所在城市</th>\n",
       "      <th>清洁费</th>\n",
       "      <th>首次评论日期</th>\n",
       "      <th>房主是否有个人资料图片</th>\n",
       "      <th>房主身份是否验证</th>\n",
       "      <th>房主回复率</th>\n",
       "      <th>何时成为房主</th>\n",
       "      <th>是否支持随即预订</th>\n",
       "      <th>最近评论日期</th>\n",
       "      <th>维度</th>\n",
       "      <th>经度</th>\n",
       "      <th>民宿周边</th>\n",
       "      <th>评论个数</th>\n",
       "      <th>房产类型</th>\n",
       "      <th>民宿评分</th>\n",
       "      <th>房型</th>\n",
       "      <th>邮编</th>\n",
       "      <th>价格</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>train_0</td>\n",
       "      <td>4</td>\n",
       "      <td>{TV,\"Cable TV\",Internet,\"Wireless Internet\",\"A...</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-05-07</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2015-02-25</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-06-26</td>\n",
       "      <td>34.109039</td>\n",
       "      <td>-118.273390</td>\n",
       "      <td>Los Feliz</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>97.0</td>\n",
       "      <td>0</td>\n",
       "      <td>90027</td>\n",
       "      <td>64.918531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>train_1</td>\n",
       "      <td>2</td>\n",
       "      <td>{TV,\"Wireless Internet\",Kitchen,\"Free parking ...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-07-02</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2009-10-27</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-07-31</td>\n",
       "      <td>40.812897</td>\n",
       "      <td>-73.919163</td>\n",
       "      <td>Mott Haven</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10454</td>\n",
       "      <td>54.918531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>train_2</td>\n",
       "      <td>4</td>\n",
       "      <td>{TV,\"Air conditioning\",Kitchen,Heating,\"Smoke ...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-07-01</td>\n",
       "      <td>t</td>\n",
       "      <td>f</td>\n",
       "      <td>100%</td>\n",
       "      <td>2017-06-29</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-07-31</td>\n",
       "      <td>40.737643</td>\n",
       "      <td>-73.953309</td>\n",
       "      <td>Greenpoint</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>0</td>\n",
       "      <td>11222</td>\n",
       "      <td>73.219281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>train_3</td>\n",
       "      <td>2</td>\n",
       "      <td>{}</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2013-03-19</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.759935</td>\n",
       "      <td>-122.420558</td>\n",
       "      <td>Mission District</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>94110</td>\n",
       "      <td>64.093909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>train_4</td>\n",
       "      <td>3</td>\n",
       "      <td>{Internet,\"Wireless Internet\",\"Air conditionin...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2014-04-30</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>100%</td>\n",
       "      <td>2011-07-30</td>\n",
       "      <td>0</td>\n",
       "      <td>2016-05-22</td>\n",
       "      <td>40.683363</td>\n",
       "      <td>-73.949490</td>\n",
       "      <td>Bedford-Stuyvesant</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>99.0</td>\n",
       "      <td>0</td>\n",
       "      <td>11216</td>\n",
       "      <td>68.454901</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      数据ID  容纳人数                                               便利设施  洗手间数量  \\\n",
       "0  train_0     4  {TV,\"Cable TV\",Internet,\"Wireless Internet\",\"A...    1.5   \n",
       "1  train_1     2  {TV,\"Wireless Internet\",Kitchen,\"Free parking ...    1.0   \n",
       "2  train_2     4  {TV,\"Air conditioning\",Kitchen,Heating,\"Smoke ...    1.0   \n",
       "3  train_3     2                                                 {}    1.0   \n",
       "4  train_4     3  {Internet,\"Wireless Internet\",\"Air conditionin...    1.0   \n",
       "\n",
       "   床的数量  床的类型  卧室数量  取消条款  所在城市  清洁费      首次评论日期 房主是否有个人资料图片 房主身份是否验证 房主回复率  \\\n",
       "0   3.0     4   2.0     0     3    0  2015-05-07           t        t   NaN   \n",
       "1   1.0     4   1.0     2     4    1  2016-07-02           t        t   NaN   \n",
       "2   2.0     4   0.0     2     4    1  2017-07-01           t        f  100%   \n",
       "3   1.0     4   1.0     0     5    1         NaN           t        t   NaN   \n",
       "4   1.0     4   1.0     1     4    1  2014-04-30           t        t  100%   \n",
       "\n",
       "       何时成为房主  是否支持随即预订      最近评论日期         维度          经度  \\\n",
       "0  2015-02-25         0  2016-06-26  34.109039 -118.273390   \n",
       "1  2009-10-27         1  2016-07-31  40.812897  -73.919163   \n",
       "2  2017-06-29         1  2017-07-31  40.737643  -73.953309   \n",
       "3  2013-03-19         0         NaN  37.759935 -122.420558   \n",
       "4  2011-07-30         0  2016-05-22  40.683363  -73.949490   \n",
       "\n",
       "                 民宿周边  评论个数  房产类型  民宿评分  房型     邮编         价格  \n",
       "0           Los Feliz    12    17  97.0   0  90027  64.918531  \n",
       "1          Mott Haven     6     0  87.0   0  10454  54.918531  \n",
       "2          Greenpoint     4     0  80.0   0  11222  73.219281  \n",
       "3    Mission District     0     0   NaN   1  94110  64.093909  \n",
       "4  Bedford-Stuyvesant    16     0  99.0   0  11216  68.454901  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train = pd.read_csv('raw_data/train.csv')\n",
    "\n",
    "print(train.shape)\n",
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(14823, 25)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数据ID</th>\n",
       "      <th>容纳人数</th>\n",
       "      <th>便利设施</th>\n",
       "      <th>洗手间数量</th>\n",
       "      <th>床的数量</th>\n",
       "      <th>床的类型</th>\n",
       "      <th>卧室数量</th>\n",
       "      <th>取消条款</th>\n",
       "      <th>所在城市</th>\n",
       "      <th>清洁费</th>\n",
       "      <th>首次评论日期</th>\n",
       "      <th>房主是否有个人资料图片</th>\n",
       "      <th>房主身份是否验证</th>\n",
       "      <th>房主回复率</th>\n",
       "      <th>何时成为房主</th>\n",
       "      <th>是否支持随即预订</th>\n",
       "      <th>最近评论日期</th>\n",
       "      <th>维度</th>\n",
       "      <th>经度</th>\n",
       "      <th>民宿周边</th>\n",
       "      <th>评论个数</th>\n",
       "      <th>房产类型</th>\n",
       "      <th>民宿评分</th>\n",
       "      <th>房型</th>\n",
       "      <th>邮编</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>test_0</td>\n",
       "      <td>2</td>\n",
       "      <td>{TV,Internet,\"Wireless Internet\",\"Air conditio...</td>\n",
       "      <td>1.5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-05-25</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>100%</td>\n",
       "      <td>2015-05-20</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-01-01</td>\n",
       "      <td>41.849684</td>\n",
       "      <td>-87.676270</td>\n",
       "      <td>Pilsen</td>\n",
       "      <td>17</td>\n",
       "      <td>17</td>\n",
       "      <td>97.0</td>\n",
       "      <td>1</td>\n",
       "      <td>60608</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>test_1</td>\n",
       "      <td>2</td>\n",
       "      <td>{TV,Internet,\"Wireless Internet\",\"Air conditio...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-11-09</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>100%</td>\n",
       "      <td>2015-09-08</td>\n",
       "      <td>0</td>\n",
       "      <td>2015-11-15</td>\n",
       "      <td>34.068613</td>\n",
       "      <td>-118.246455</td>\n",
       "      <td>Echo Park</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>0</td>\n",
       "      <td>90012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>test_2</td>\n",
       "      <td>5</td>\n",
       "      <td>{TV,\"Cable TV\",\"Wireless Internet\",\"Air condit...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-05-15</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>100%</td>\n",
       "      <td>2017-05-06</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-09-25</td>\n",
       "      <td>40.701958</td>\n",
       "      <td>-73.917352</td>\n",
       "      <td>Bushwick</td>\n",
       "      <td>25</td>\n",
       "      <td>0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>0</td>\n",
       "      <td>11237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>test_3</td>\n",
       "      <td>6</td>\n",
       "      <td>{\"Cable TV\",Internet,\"Wireless Internet\",\"Air ...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2012-11-12</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>70%</td>\n",
       "      <td>2009-02-06</td>\n",
       "      <td>0</td>\n",
       "      <td>2017-07-29</td>\n",
       "      <td>40.742959</td>\n",
       "      <td>-73.990820</td>\n",
       "      <td>Flatiron District</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>0</td>\n",
       "      <td>10010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>test_4</td>\n",
       "      <td>2</td>\n",
       "      <td>{Internet,\"Wireless Internet\",\"Air conditionin...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-02-17</td>\n",
       "      <td>t</td>\n",
       "      <td>t</td>\n",
       "      <td>100%</td>\n",
       "      <td>2015-10-20</td>\n",
       "      <td>0</td>\n",
       "      <td>2017-03-25</td>\n",
       "      <td>34.046473</td>\n",
       "      <td>-117.734095</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>17</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1</td>\n",
       "      <td>91766</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     数据ID  容纳人数                                               便利设施  洗手间数量  \\\n",
       "0  test_0     2  {TV,Internet,\"Wireless Internet\",\"Air conditio...    1.5   \n",
       "1  test_1     2  {TV,Internet,\"Wireless Internet\",\"Air conditio...    2.0   \n",
       "2  test_2     5  {TV,\"Cable TV\",\"Wireless Internet\",\"Air condit...    1.0   \n",
       "3  test_3     6  {\"Cable TV\",Internet,\"Wireless Internet\",\"Air ...    1.0   \n",
       "4  test_4     2  {Internet,\"Wireless Internet\",\"Air conditionin...    1.0   \n",
       "\n",
       "   床的数量  床的类型  卧室数量  取消条款  所在城市  清洁费      首次评论日期 房主是否有个人资料图片 房主身份是否验证 房主回复率  \\\n",
       "0   1.0     4   1.0     2     1    1  2015-05-25           t        t  100%   \n",
       "1   1.0     4   1.0     2     3    1  2015-11-09           t        t  100%   \n",
       "2   3.0     4   2.0     1     4    1  2017-05-15           t        t  100%   \n",
       "3   3.0     4   1.0     2     4    1  2012-11-12           t        t   70%   \n",
       "4   1.0     4   1.0     0     3    1  2017-02-17           t        t  100%   \n",
       "\n",
       "       何时成为房主  是否支持随即预订      最近评论日期         维度          经度               民宿周边  \\\n",
       "0  2015-05-20         1  2017-01-01  41.849684  -87.676270             Pilsen   \n",
       "1  2015-09-08         0  2015-11-15  34.068613 -118.246455          Echo Park   \n",
       "2  2017-05-06         1  2017-09-25  40.701958  -73.917352           Bushwick   \n",
       "3  2009-02-06         0  2017-07-29  40.742959  -73.990820  Flatiron District   \n",
       "4  2015-10-20         0  2017-03-25  34.046473 -117.734095                NaN   \n",
       "\n",
       "   评论个数  房产类型   民宿评分  房型     邮编  \n",
       "0    17    17   97.0   1  60608  \n",
       "1     2     0  100.0   0  90012  \n",
       "2    25     0   88.0   0  11237  \n",
       "3    12     0   82.0   0  10010  \n",
       "4     2    17  100.0   1  91766  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test = pd.read_csv('raw_data/test.csv')\n",
    "\n",
    "print(test.shape)\n",
    "test.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(74111, 26)\n"
     ]
    }
   ],
   "source": [
    "df_features = pd.concat([train, test])\n",
    "\n",
    "print(df_features.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据填充和清洗\n",
    "\n",
    "df_features['洗手间数量'].fillna(-1, inplace=True)\n",
    "df_features['床的数量'].fillna(-1, inplace=True)\n",
    "df_features['卧室数量'].fillna(-1, inplace=True)\n",
    "df_features['房主是否有个人资料图片'].fillna('na', inplace=True)\n",
    "df_features['房主身份是否验证'].fillna('na', inplace=True)        # 与上面特征是一样的, 可以去掉\n",
    "df_features['房主回复率'].fillna('-1', inplace=True)\n",
    "df_features['房主回复率'] = df_features['房主回复率'].astype(str).apply(lambda x: x.replace('%', ''))\n",
    "df_features['房主回复率'] = df_features['房主回复率'].astype(int)\n",
    "df_features['民宿周边'].fillna('na', inplace=True)\n",
    "mean_score = df_features['民宿评分'].mean()\n",
    "df_features['民宿评分'].fillna(mean_score, inplace=True)\n",
    "df_features['邮编'].fillna('na', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "for feat in ['房主是否有个人资料图片', '房主身份是否验证', '民宿周边', '邮编']:\n",
    "    lbl = LabelEncoder()\n",
    "    lbl.fit(df_features[feat])\n",
    "    df_features[feat] = lbl.transform(df_features[feat])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def freq_enc(df, col):\n",
    "    vc = df[col].value_counts(dropna=True, normalize=True).to_dict()\n",
    "    df[f'{col}_freq'] = df[col].map(vc)\n",
    "    return df\n",
    "\n",
    "for feat in ['容纳人数', '洗手间数量', '床的数量', '床的类型',\n",
    "             '卧室数量', '取消条款', '所在城市', '清洁费', \n",
    "             '房主是否有个人资料图片', '房主回复率', '是否支持随即预订',\n",
    "             '民宿周边', '房产类型', '房型', '邮编']:\n",
    "    df_features = freq_enc(df_features, feat)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Target Encoding\n",
    "\n",
    "# def stat(df, df_merge, group_by, agg):\n",
    "#     group = df.groupby(group_by).agg(agg)\n",
    "\n",
    "#     columns = []\n",
    "#     for on, methods in agg.items():\n",
    "#         for method in methods:\n",
    "#             columns.append('{}_{}_{}'.format('_'.join(group_by), on, method))\n",
    "#     group.columns = columns\n",
    "#     group.reset_index(inplace=True)\n",
    "#     df_merge = df_merge.merge(group, on=group_by, how='left')\n",
    "\n",
    "#     del (group)\n",
    "#     gc.collect()\n",
    "#     return df_merge\n",
    "    \n",
    "\n",
    "# def statis_feat(df_know, df_unknow):\n",
    "#     df_unknow = stat(df_know, df_unknow, ['所在城市'], {'价格': ['mean']})\n",
    "# #     df_unknow = stat(df_know, df_unknow, ['邮编'], {'价格': ['mean', 'std', 'max']})\n",
    "\n",
    "#     return df_unknow\n",
    "    \n",
    "    \n",
    "\n",
    "# # 5折交叉\n",
    "# df_train = df_features[~df_features['价格'].isnull()]\n",
    "# df_train = df_train.reset_index(drop=True)\n",
    "# df_test = df_features[df_features['价格'].isnull()]\n",
    "\n",
    "# df_stas_feat = None\n",
    "# kf = KFold(n_splits=5, random_state=2021, shuffle=True)\n",
    "# for train_index, val_index in kf.split(df_train):\n",
    "#     df_fold_train = df_train.iloc[train_index]\n",
    "#     df_fold_val = df_train.iloc[val_index]\n",
    "\n",
    "#     df_fold_val = statis_feat(df_fold_train, df_fold_val)\n",
    "#     df_stas_feat = pd.concat([df_stas_feat, df_fold_val], axis=0)\n",
    "\n",
    "#     del(df_fold_train)\n",
    "#     del(df_fold_val)\n",
    "#     gc.collect()\n",
    "\n",
    "# df_test = statis_feat(df_train, df_test)\n",
    "# df_features = pd.concat([df_stas_feat, df_test], axis=0)\n",
    "\n",
    "# del(df_stas_feat)\n",
    "# del(df_train)\n",
    "# del(df_test)\n",
    "# gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数值交叉特征\n",
    "\n",
    "df_features['人均床数量'] = df_features['容纳人数'] / (df_features['床的数量'] + 1e-3)\n",
    "df_features['人均卧室量'] = df_features['容纳人数'] / (df_features['卧室数量'] + 1e-3)\n",
    "df_features['卧室床均量'] = df_features['床的数量'] / (df_features['卧室数量'] + 1e-3)\n",
    "df_features['经纬度平方根'] = (df_features['维度']*df_features['维度'] + df_features['经度']*df_features['经度'])**.5\n",
    "\n",
    "df_features['城市最大维度'] = df_features.groupby(['所在城市'])['维度'].transform('max')\n",
    "df_features['城市最小维度'] = df_features.groupby(['所在城市'])['维度'].transform('min')\n",
    "df_features['城市维度跨度'] = df_features['城市最大维度'] - df_features['城市最小维度']\n",
    "df_features['城市最大经度'] = df_features.groupby(['所在城市'])['经度'].transform('max')\n",
    "df_features['城市最小经度'] = df_features.groupby(['所在城市'])['经度'].transform('min')\n",
    "df_features['城市经度跨度'] = df_features['城市最大经度'] - df_features['城市最小经度']\n",
    "df_features['城市面积'] = df_features['城市维度跨度'] * df_features['城市经度跨度']\n",
    "df_features['城市总房数'] = df_features.groupby(['所在城市'])['数据ID'].transform('count')\n",
    "df_features['城市房密度'] = df_features['城市面积'] / df_features['城市总房数']\n",
    "df_features.drop(['城市最大维度', '城市最小维度', '城市最大经度', '城市最小经度', '城市总房数'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 时间特征处理\n",
    "\n",
    "df_features['首次评论日期'] = pd.to_datetime(df_features['首次评论日期']).values.astype(np.int64) // 10 ** 9\n",
    "df_features['何时成为房主'] = pd.to_datetime(df_features['何时成为房主']).values.astype(np.int64) // 10 ** 9\n",
    "df_features['最近评论日期'] = pd.to_datetime(df_features['最近评论日期']).values.astype(np.int64) // 10 ** 9\n",
    "\n",
    "df_features['timestamp_diff1'] = df_features['首次评论日期'] - df_features['何时成为房主']\n",
    "df_features['timestamp_diff2'] = df_features['最近评论日期'] - df_features['首次评论日期']\n",
    "df_features['timestamp_diff3'] = df_features['最近评论日期'] - df_features['何时成为房主']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "55b32eda1d82407aa95b4c3916ff4ad5",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, max=4.0), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "# 统计特征\n",
    "\n",
    "def brute_force(df, features, groups):\n",
    "    for method in tqdm(['max', 'min', 'mean', 'std']):\n",
    "        for feature in features:\n",
    "            for group in groups:\n",
    "                df[f'{group}_{feature}_{method}'] = df.groupby(group)[feature].transform(method)\n",
    "                \n",
    "    return df\n",
    "\n",
    "dense_feats = ['timestamp_diff1', 'timestamp_diff2', 'timestamp_diff3']\n",
    "cate_feats  = ['房型']\n",
    "\n",
    "df_features = brute_force(df_features, dense_feats, cate_feats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>数据ID</th>\n",
       "      <th>容纳人数</th>\n",
       "      <th>便利设施</th>\n",
       "      <th>洗手间数量</th>\n",
       "      <th>床的数量</th>\n",
       "      <th>床的类型</th>\n",
       "      <th>卧室数量</th>\n",
       "      <th>取消条款</th>\n",
       "      <th>所在城市</th>\n",
       "      <th>清洁费</th>\n",
       "      <th>首次评论日期</th>\n",
       "      <th>房主是否有个人资料图片</th>\n",
       "      <th>房主身份是否验证</th>\n",
       "      <th>房主回复率</th>\n",
       "      <th>何时成为房主</th>\n",
       "      <th>是否支持随即预订</th>\n",
       "      <th>最近评论日期</th>\n",
       "      <th>维度</th>\n",
       "      <th>经度</th>\n",
       "      <th>民宿周边</th>\n",
       "      <th>评论个数</th>\n",
       "      <th>房产类型</th>\n",
       "      <th>民宿评分</th>\n",
       "      <th>房型</th>\n",
       "      <th>邮编</th>\n",
       "      <th>价格</th>\n",
       "      <th>容纳人数_freq</th>\n",
       "      <th>洗手间数量_freq</th>\n",
       "      <th>床的数量_freq</th>\n",
       "      <th>床的类型_freq</th>\n",
       "      <th>卧室数量_freq</th>\n",
       "      <th>取消条款_freq</th>\n",
       "      <th>所在城市_freq</th>\n",
       "      <th>清洁费_freq</th>\n",
       "      <th>房主是否有个人资料图片_freq</th>\n",
       "      <th>房主回复率_freq</th>\n",
       "      <th>是否支持随即预订_freq</th>\n",
       "      <th>民宿周边_freq</th>\n",
       "      <th>房产类型_freq</th>\n",
       "      <th>房型_freq</th>\n",
       "      <th>邮编_freq</th>\n",
       "      <th>人均床数量</th>\n",
       "      <th>人均卧室量</th>\n",
       "      <th>卧室床均量</th>\n",
       "      <th>经纬度平方根</th>\n",
       "      <th>城市维度跨度</th>\n",
       "      <th>城市经度跨度</th>\n",
       "      <th>城市面积</th>\n",
       "      <th>城市房密度</th>\n",
       "      <th>timestamp_diff1</th>\n",
       "      <th>timestamp_diff2</th>\n",
       "      <th>timestamp_diff3</th>\n",
       "      <th>房型_timestamp_diff1_max</th>\n",
       "      <th>房型_timestamp_diff2_max</th>\n",
       "      <th>房型_timestamp_diff3_max</th>\n",
       "      <th>房型_timestamp_diff1_min</th>\n",
       "      <th>房型_timestamp_diff2_min</th>\n",
       "      <th>房型_timestamp_diff3_min</th>\n",
       "      <th>房型_timestamp_diff1_mean</th>\n",
       "      <th>房型_timestamp_diff2_mean</th>\n",
       "      <th>房型_timestamp_diff3_mean</th>\n",
       "      <th>房型_timestamp_diff1_std</th>\n",
       "      <th>房型_timestamp_diff2_std</th>\n",
       "      <th>房型_timestamp_diff3_std</th>\n",
       "      <th>便利设施_tfidf_0</th>\n",
       "      <th>便利设施_tfidf_1</th>\n",
       "      <th>便利设施_tfidf_2</th>\n",
       "      <th>便利设施_tfidf_3</th>\n",
       "      <th>便利设施_tfidf_4</th>\n",
       "      <th>便利设施_tfidf_5</th>\n",
       "      <th>便利设施_tfidf_6</th>\n",
       "      <th>便利设施_tfidf_7</th>\n",
       "      <th>便利设施_tfidf_8</th>\n",
       "      <th>便利设施_tfidf_9</th>\n",
       "      <th>便利设施_tfidf_10</th>\n",
       "      <th>便利设施_tfidf_11</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>train_0</td>\n",
       "      <td>4</td>\n",
       "      <td>TV Cable TV Internet Wireless Internet Air con...</td>\n",
       "      <td>1.5</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>1430956800</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>1424822400</td>\n",
       "      <td>0</td>\n",
       "      <td>1466899200</td>\n",
       "      <td>34.109039</td>\n",
       "      <td>-118.273390</td>\n",
       "      <td>323</td>\n",
       "      <td>12</td>\n",
       "      <td>17</td>\n",
       "      <td>97.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>454</td>\n",
       "      <td>64.918531</td>\n",
       "      <td>0.162810</td>\n",
       "      <td>0.051288</td>\n",
       "      <td>0.086924</td>\n",
       "      <td>0.971894</td>\n",
       "      <td>0.153162</td>\n",
       "      <td>0.304206</td>\n",
       "      <td>0.302964</td>\n",
       "      <td>0.265925</td>\n",
       "      <td>0.994414</td>\n",
       "      <td>0.246913</td>\n",
       "      <td>0.737542</td>\n",
       "      <td>0.002672</td>\n",
       "      <td>0.222787</td>\n",
       "      <td>0.557407</td>\n",
       "      <td>0.005411</td>\n",
       "      <td>1.332889</td>\n",
       "      <td>1.999000</td>\n",
       "      <td>1.499250</td>\n",
       "      <td>123.093547</td>\n",
       "      <td>1.393795</td>\n",
       "      <td>1.255555</td>\n",
       "      <td>1.749986</td>\n",
       "      <td>0.000078</td>\n",
       "      <td>6134400</td>\n",
       "      <td>35942400</td>\n",
       "      <td>42076800</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10730188037</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>0</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>-1.961812e+09</td>\n",
       "      <td>3.582431e+07</td>\n",
       "      <td>-1.925987e+09</td>\n",
       "      <td>4.222271e+09</td>\n",
       "      <td>2.267070e+08</td>\n",
       "      <td>4.237623e+09</td>\n",
       "      <td>0.485278</td>\n",
       "      <td>0.345060</td>\n",
       "      <td>-0.048676</td>\n",
       "      <td>-0.266940</td>\n",
       "      <td>-0.294044</td>\n",
       "      <td>0.162518</td>\n",
       "      <td>-0.115719</td>\n",
       "      <td>-0.066753</td>\n",
       "      <td>0.012276</td>\n",
       "      <td>0.114879</td>\n",
       "      <td>0.188774</td>\n",
       "      <td>0.141827</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>train_1</td>\n",
       "      <td>2</td>\n",
       "      <td>TV Wireless Internet Kitchen Free parking on p...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1467417600</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>1256601600</td>\n",
       "      <td>1</td>\n",
       "      <td>1469923200</td>\n",
       "      <td>40.812897</td>\n",
       "      <td>-73.919163</td>\n",
       "      <td>371</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>87.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>150</td>\n",
       "      <td>54.918531</td>\n",
       "      <td>0.429815</td>\n",
       "      <td>0.783946</td>\n",
       "      <td>0.609140</td>\n",
       "      <td>0.971894</td>\n",
       "      <td>0.671749</td>\n",
       "      <td>0.436831</td>\n",
       "      <td>0.436494</td>\n",
       "      <td>0.734075</td>\n",
       "      <td>0.994414</td>\n",
       "      <td>0.246913</td>\n",
       "      <td>0.262458</td>\n",
       "      <td>0.000594</td>\n",
       "      <td>0.661211</td>\n",
       "      <td>0.557407</td>\n",
       "      <td>0.000337</td>\n",
       "      <td>1.998002</td>\n",
       "      <td>1.998002</td>\n",
       "      <td>0.999001</td>\n",
       "      <td>84.437759</td>\n",
       "      <td>0.409380</td>\n",
       "      <td>0.543143</td>\n",
       "      <td>0.222352</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>210816000</td>\n",
       "      <td>2505600</td>\n",
       "      <td>213321600</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10730188037</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>0</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>-1.961812e+09</td>\n",
       "      <td>3.582431e+07</td>\n",
       "      <td>-1.925987e+09</td>\n",
       "      <td>4.222271e+09</td>\n",
       "      <td>2.267070e+08</td>\n",
       "      <td>4.237623e+09</td>\n",
       "      <td>0.654590</td>\n",
       "      <td>0.014672</td>\n",
       "      <td>-0.260082</td>\n",
       "      <td>-0.125328</td>\n",
       "      <td>-0.065055</td>\n",
       "      <td>-0.054602</td>\n",
       "      <td>0.175410</td>\n",
       "      <td>-0.184152</td>\n",
       "      <td>0.017464</td>\n",
       "      <td>-0.022459</td>\n",
       "      <td>-0.174326</td>\n",
       "      <td>0.004266</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>train_2</td>\n",
       "      <td>4</td>\n",
       "      <td>TV Air conditioning Kitchen Heating Smoke dete...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1498867200</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>100</td>\n",
       "      <td>1498694400</td>\n",
       "      <td>1</td>\n",
       "      <td>1501459200</td>\n",
       "      <td>40.737643</td>\n",
       "      <td>-73.953309</td>\n",
       "      <td>238</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>231</td>\n",
       "      <td>73.219281</td>\n",
       "      <td>0.162810</td>\n",
       "      <td>0.783946</td>\n",
       "      <td>0.225392</td>\n",
       "      <td>0.971894</td>\n",
       "      <td>0.090607</td>\n",
       "      <td>0.436831</td>\n",
       "      <td>0.436494</td>\n",
       "      <td>0.734075</td>\n",
       "      <td>0.994414</td>\n",
       "      <td>0.583638</td>\n",
       "      <td>0.262458</td>\n",
       "      <td>0.009783</td>\n",
       "      <td>0.661211</td>\n",
       "      <td>0.557407</td>\n",
       "      <td>0.009958</td>\n",
       "      <td>1.999000</td>\n",
       "      <td>4000.000000</td>\n",
       "      <td>2000.000000</td>\n",
       "      <td>84.431318</td>\n",
       "      <td>0.409380</td>\n",
       "      <td>0.543143</td>\n",
       "      <td>0.222352</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>172800</td>\n",
       "      <td>2592000</td>\n",
       "      <td>2764800</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10730188037</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>0</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>-1.961812e+09</td>\n",
       "      <td>3.582431e+07</td>\n",
       "      <td>-1.925987e+09</td>\n",
       "      <td>4.222271e+09</td>\n",
       "      <td>2.267070e+08</td>\n",
       "      <td>4.237623e+09</td>\n",
       "      <td>0.374496</td>\n",
       "      <td>-0.068823</td>\n",
       "      <td>-0.017293</td>\n",
       "      <td>-0.021660</td>\n",
       "      <td>0.205175</td>\n",
       "      <td>0.184736</td>\n",
       "      <td>0.317999</td>\n",
       "      <td>-0.110520</td>\n",
       "      <td>-0.015582</td>\n",
       "      <td>0.102508</td>\n",
       "      <td>0.100090</td>\n",
       "      <td>-0.081543</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>train_3</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>-9223372037</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-1</td>\n",
       "      <td>1363651200</td>\n",
       "      <td>0</td>\n",
       "      <td>-9223372037</td>\n",
       "      <td>37.759935</td>\n",
       "      <td>-122.420558</td>\n",
       "      <td>356</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>94.067365</td>\n",
       "      <td>1</td>\n",
       "      <td>739</td>\n",
       "      <td>64.093909</td>\n",
       "      <td>0.429815</td>\n",
       "      <td>0.783946</td>\n",
       "      <td>0.609140</td>\n",
       "      <td>0.971894</td>\n",
       "      <td>0.671749</td>\n",
       "      <td>0.304206</td>\n",
       "      <td>0.086816</td>\n",
       "      <td>0.734075</td>\n",
       "      <td>0.994414</td>\n",
       "      <td>0.246913</td>\n",
       "      <td>0.737542</td>\n",
       "      <td>0.010579</td>\n",
       "      <td>0.661211</td>\n",
       "      <td>0.413407</td>\n",
       "      <td>0.013331</td>\n",
       "      <td>1.998002</td>\n",
       "      <td>1.998002</td>\n",
       "      <td>0.999001</td>\n",
       "      <td>128.111692</td>\n",
       "      <td>0.121451</td>\n",
       "      <td>0.146278</td>\n",
       "      <td>0.017766</td>\n",
       "      <td>0.000003</td>\n",
       "      <td>-10587023237</td>\n",
       "      <td>0</td>\n",
       "      <td>-10587023237</td>\n",
       "      <td>10729064837</td>\n",
       "      <td>10730188037</td>\n",
       "      <td>10730188037</td>\n",
       "      <td>-10730447237</td>\n",
       "      <td>0</td>\n",
       "      <td>-10730447237</td>\n",
       "      <td>-2.466029e+09</td>\n",
       "      <td>3.290898e+07</td>\n",
       "      <td>-2.433120e+09</td>\n",
       "      <td>4.600638e+09</td>\n",
       "      <td>2.619817e+08</td>\n",
       "      <td>4.611573e+09</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>train_4</td>\n",
       "      <td>3</td>\n",
       "      <td>Internet Wireless Internet Air conditioning Ki...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1398816000</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>100</td>\n",
       "      <td>1311984000</td>\n",
       "      <td>0</td>\n",
       "      <td>1463875200</td>\n",
       "      <td>40.683363</td>\n",
       "      <td>-73.949490</td>\n",
       "      <td>44</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>220</td>\n",
       "      <td>68.454901</td>\n",
       "      <td>0.105167</td>\n",
       "      <td>0.783946</td>\n",
       "      <td>0.609140</td>\n",
       "      <td>0.971894</td>\n",
       "      <td>0.671749</td>\n",
       "      <td>0.257222</td>\n",
       "      <td>0.436494</td>\n",
       "      <td>0.734075</td>\n",
       "      <td>0.994414</td>\n",
       "      <td>0.583638</td>\n",
       "      <td>0.737542</td>\n",
       "      <td>0.029226</td>\n",
       "      <td>0.661211</td>\n",
       "      <td>0.557407</td>\n",
       "      <td>0.008231</td>\n",
       "      <td>2.997003</td>\n",
       "      <td>2.997003</td>\n",
       "      <td>0.999001</td>\n",
       "      <td>84.401796</td>\n",
       "      <td>0.409380</td>\n",
       "      <td>0.543143</td>\n",
       "      <td>0.222352</td>\n",
       "      <td>0.000007</td>\n",
       "      <td>86832000</td>\n",
       "      <td>65059200</td>\n",
       "      <td>151891200</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10729756037</td>\n",
       "      <td>10730188037</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>0</td>\n",
       "      <td>-10730188037</td>\n",
       "      <td>-1.961812e+09</td>\n",
       "      <td>3.582431e+07</td>\n",
       "      <td>-1.925987e+09</td>\n",
       "      <td>4.222271e+09</td>\n",
       "      <td>2.267070e+08</td>\n",
       "      <td>4.237623e+09</td>\n",
       "      <td>0.451925</td>\n",
       "      <td>0.369690</td>\n",
       "      <td>-0.052787</td>\n",
       "      <td>-0.329882</td>\n",
       "      <td>-0.366512</td>\n",
       "      <td>0.120876</td>\n",
       "      <td>-0.102477</td>\n",
       "      <td>0.103181</td>\n",
       "      <td>-0.026274</td>\n",
       "      <td>-0.189920</td>\n",
       "      <td>-0.063034</td>\n",
       "      <td>-0.072947</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      数据ID  容纳人数                                               便利设施  洗手间数量  \\\n",
       "0  train_0     4  TV Cable TV Internet Wireless Internet Air con...    1.5   \n",
       "1  train_1     2  TV Wireless Internet Kitchen Free parking on p...    1.0   \n",
       "2  train_2     4  TV Air conditioning Kitchen Heating Smoke dete...    1.0   \n",
       "3  train_3     2                                                       1.0   \n",
       "4  train_4     3  Internet Wireless Internet Air conditioning Ki...    1.0   \n",
       "\n",
       "   床的数量  床的类型  卧室数量  取消条款  所在城市  清洁费      首次评论日期  房主是否有个人资料图片  房主身份是否验证  \\\n",
       "0   3.0     4   2.0     0     3    0  1430956800            2         2   \n",
       "1   1.0     4   1.0     2     4    1  1467417600            2         2   \n",
       "2   2.0     4   0.0     2     4    1  1498867200            2         0   \n",
       "3   1.0     4   1.0     0     5    1 -9223372037            2         2   \n",
       "4   1.0     4   1.0     1     4    1  1398816000            2         2   \n",
       "\n",
       "   房主回复率      何时成为房主  是否支持随即预订      最近评论日期         维度          经度  民宿周边  评论个数  \\\n",
       "0     -1  1424822400         0  1466899200  34.109039 -118.273390   323    12   \n",
       "1     -1  1256601600         1  1469923200  40.812897  -73.919163   371     6   \n",
       "2    100  1498694400         1  1501459200  40.737643  -73.953309   238     4   \n",
       "3     -1  1363651200         0 -9223372037  37.759935 -122.420558   356     0   \n",
       "4    100  1311984000         0  1463875200  40.683363  -73.949490    44    16   \n",
       "\n",
       "   房产类型       民宿评分  房型   邮编         价格  容纳人数_freq  洗手间数量_freq  床的数量_freq  \\\n",
       "0    17  97.000000   0  454  64.918531   0.162810    0.051288   0.086924   \n",
       "1     0  87.000000   0  150  54.918531   0.429815    0.783946   0.609140   \n",
       "2     0  80.000000   0  231  73.219281   0.162810    0.783946   0.225392   \n",
       "3     0  94.067365   1  739  64.093909   0.429815    0.783946   0.609140   \n",
       "4     0  99.000000   0  220  68.454901   0.105167    0.783946   0.609140   \n",
       "\n",
       "   床的类型_freq  卧室数量_freq  取消条款_freq  所在城市_freq  清洁费_freq  房主是否有个人资料图片_freq  \\\n",
       "0   0.971894   0.153162   0.304206   0.302964  0.265925          0.994414   \n",
       "1   0.971894   0.671749   0.436831   0.436494  0.734075          0.994414   \n",
       "2   0.971894   0.090607   0.436831   0.436494  0.734075          0.994414   \n",
       "3   0.971894   0.671749   0.304206   0.086816  0.734075          0.994414   \n",
       "4   0.971894   0.671749   0.257222   0.436494  0.734075          0.994414   \n",
       "\n",
       "   房主回复率_freq  是否支持随即预订_freq  民宿周边_freq  房产类型_freq   房型_freq   邮编_freq  \\\n",
       "0    0.246913       0.737542   0.002672   0.222787  0.557407  0.005411   \n",
       "1    0.246913       0.262458   0.000594   0.661211  0.557407  0.000337   \n",
       "2    0.583638       0.262458   0.009783   0.661211  0.557407  0.009958   \n",
       "3    0.246913       0.737542   0.010579   0.661211  0.413407  0.013331   \n",
       "4    0.583638       0.737542   0.029226   0.661211  0.557407  0.008231   \n",
       "\n",
       "      人均床数量        人均卧室量        卧室床均量      经纬度平方根    城市维度跨度    城市经度跨度  \\\n",
       "0  1.332889     1.999000     1.499250  123.093547  1.393795  1.255555   \n",
       "1  1.998002     1.998002     0.999001   84.437759  0.409380  0.543143   \n",
       "2  1.999000  4000.000000  2000.000000   84.431318  0.409380  0.543143   \n",
       "3  1.998002     1.998002     0.999001  128.111692  0.121451  0.146278   \n",
       "4  2.997003     2.997003     0.999001   84.401796  0.409380  0.543143   \n",
       "\n",
       "       城市面积     城市房密度  timestamp_diff1  timestamp_diff2  timestamp_diff3  \\\n",
       "0  1.749986  0.000078          6134400         35942400         42076800   \n",
       "1  0.222352  0.000007        210816000          2505600        213321600   \n",
       "2  0.222352  0.000007           172800          2592000          2764800   \n",
       "3  0.017766  0.000003     -10587023237                0     -10587023237   \n",
       "4  0.222352  0.000007         86832000         65059200        151891200   \n",
       "\n",
       "   房型_timestamp_diff1_max  房型_timestamp_diff2_max  房型_timestamp_diff3_max  \\\n",
       "0             10729756037             10729756037             10730188037   \n",
       "1             10729756037             10729756037             10730188037   \n",
       "2             10729756037             10729756037             10730188037   \n",
       "3             10729064837             10730188037             10730188037   \n",
       "4             10729756037             10729756037             10730188037   \n",
       "\n",
       "   房型_timestamp_diff1_min  房型_timestamp_diff2_min  房型_timestamp_diff3_min  \\\n",
       "0            -10730188037                       0            -10730188037   \n",
       "1            -10730188037                       0            -10730188037   \n",
       "2            -10730188037                       0            -10730188037   \n",
       "3            -10730447237                       0            -10730447237   \n",
       "4            -10730188037                       0            -10730188037   \n",
       "\n",
       "   房型_timestamp_diff1_mean  房型_timestamp_diff2_mean  房型_timestamp_diff3_mean  \\\n",
       "0            -1.961812e+09             3.582431e+07            -1.925987e+09   \n",
       "1            -1.961812e+09             3.582431e+07            -1.925987e+09   \n",
       "2            -1.961812e+09             3.582431e+07            -1.925987e+09   \n",
       "3            -2.466029e+09             3.290898e+07            -2.433120e+09   \n",
       "4            -1.961812e+09             3.582431e+07            -1.925987e+09   \n",
       "\n",
       "   房型_timestamp_diff1_std  房型_timestamp_diff2_std  房型_timestamp_diff3_std  \\\n",
       "0            4.222271e+09            2.267070e+08            4.237623e+09   \n",
       "1            4.222271e+09            2.267070e+08            4.237623e+09   \n",
       "2            4.222271e+09            2.267070e+08            4.237623e+09   \n",
       "3            4.600638e+09            2.619817e+08            4.611573e+09   \n",
       "4            4.222271e+09            2.267070e+08            4.237623e+09   \n",
       "\n",
       "   便利设施_tfidf_0  便利设施_tfidf_1  便利设施_tfidf_2  便利设施_tfidf_3  便利设施_tfidf_4  \\\n",
       "0      0.485278      0.345060     -0.048676     -0.266940     -0.294044   \n",
       "1      0.654590      0.014672     -0.260082     -0.125328     -0.065055   \n",
       "2      0.374496     -0.068823     -0.017293     -0.021660      0.205175   \n",
       "3      0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "4      0.451925      0.369690     -0.052787     -0.329882     -0.366512   \n",
       "\n",
       "   便利设施_tfidf_5  便利设施_tfidf_6  便利设施_tfidf_7  便利设施_tfidf_8  便利设施_tfidf_9  \\\n",
       "0      0.162518     -0.115719     -0.066753      0.012276      0.114879   \n",
       "1     -0.054602      0.175410     -0.184152      0.017464     -0.022459   \n",
       "2      0.184736      0.317999     -0.110520     -0.015582      0.102508   \n",
       "3      0.000000      0.000000      0.000000      0.000000      0.000000   \n",
       "4      0.120876     -0.102477      0.103181     -0.026274     -0.189920   \n",
       "\n",
       "   便利设施_tfidf_10  便利设施_tfidf_11  \n",
       "0       0.188774       0.141827  \n",
       "1      -0.174326       0.004266  \n",
       "2       0.100090      -0.081543  \n",
       "3       0.000000       0.000000  \n",
       "4      -0.063034      -0.072947  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# TF-IDF + SVD encoding\n",
    "\n",
    "n_components = 12\n",
    "\n",
    "df_features['便利设施'] = df_features['便利设施'].apply(\n",
    "    lambda x: x.replace('{', '').replace('}', '').replace('\"', '').replace(':', '').replace(',', ' '))\n",
    "\n",
    "X = list(df_features['便利设施'].values)\n",
    "tfv = TfidfVectorizer(ngram_range=(1,2), max_features=10000)\n",
    "tfv.fit(X)\n",
    "X_tfidf = tfv.transform(X)\n",
    "svd = TruncatedSVD(n_components=n_components)\n",
    "svd.fit(X_tfidf)\n",
    "X_svd = svd.transform(X_tfidf)\n",
    "\n",
    "for i in range(n_components):\n",
    "    df_features[f'便利设施_tfidf_{i}'] = X_svd[:, i]\n",
    "    \n",
    "df_features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# emb_size = 2\n",
    "# # sentences = df_features['便利设施'].str.lower().values.tolist()\n",
    "# sentences = df_features['便利设施'].values.tolist()\n",
    "\n",
    "# words = []\n",
    "# for i in range(len(sentences)):\n",
    "#     sentences[i] = sentences[i].split()\n",
    "#     words += sentences[i]\n",
    "    \n",
    "# words = list(set(words))\n",
    "\n",
    "# model = Word2Vec(sentences, size=emb_size, window=3,\n",
    "#                  min_count=1, sg=0, hs=1, seed=2021)\n",
    "\n",
    "# emb_matrix_mean = []\n",
    "# emb_matrix_max = []\n",
    "\n",
    "# for seq in sentences:\n",
    "#     vec = []\n",
    "#     for w in seq:\n",
    "#         if w in model:\n",
    "#             vec.append(model[w])\n",
    "#     if len(vec) > 0:\n",
    "#         emb_matrix_mean.append(np.mean(vec, axis=0))\n",
    "#         emb_matrix_max.append(np.max(vec, axis=0))\n",
    "#     else:\n",
    "#         emb_matrix_mean.append([0] * emb_size)\n",
    "#         emb_matrix_max.append([0] * emb_size)\n",
    "\n",
    "# df_emb_mean = pd.DataFrame(emb_matrix_mean)\n",
    "# df_emb_mean.columns = ['便利设施_w2v_mean_{}'.format(\n",
    "#     i) for i in range(emb_size)]\n",
    "\n",
    "# df_emb_max = pd.DataFrame(emb_matrix_max)\n",
    "# df_emb_max.columns = ['便利设施_w2v_max_{}'.format(\n",
    "#     i) for i in range(emb_size)]\n",
    "\n",
    "# for i in range(emb_size):\n",
    "#     df_features[f'便利设施_w2v_mean_{i}'] = df_emb_mean[f'便利设施_w2v_mean_{i}']\n",
    "#     df_features[f'便利设施_w2v_max_{i}'] = df_emb_max[f'便利设施_w2v_max_{i}']\n",
    "\n",
    "# df_features.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 74111 entries, 0 to 14822\n",
      "Data columns (total 71 columns):\n",
      " #   Column                   Non-Null Count  Dtype  \n",
      "---  ------                   --------------  -----  \n",
      " 0   数据ID                     74111 non-null  object \n",
      " 1   容纳人数                     74111 non-null  int64  \n",
      " 2   洗手间数量                    74111 non-null  float64\n",
      " 3   床的数量                     74111 non-null  float64\n",
      " 4   床的类型                     74111 non-null  int64  \n",
      " 5   卧室数量                     74111 non-null  float64\n",
      " 6   取消条款                     74111 non-null  int64  \n",
      " 7   所在城市                     74111 non-null  int64  \n",
      " 8   清洁费                      74111 non-null  int64  \n",
      " 9   房主是否有个人资料图片              74111 non-null  int64  \n",
      " 10  房主回复率                    74111 non-null  int64  \n",
      " 11  是否支持随即预订                 74111 non-null  int64  \n",
      " 12  维度                       74111 non-null  float64\n",
      " 13  经度                       74111 non-null  float64\n",
      " 14  民宿周边                     74111 non-null  int64  \n",
      " 15  评论个数                     74111 non-null  int64  \n",
      " 16  房产类型                     74111 non-null  int64  \n",
      " 17  民宿评分                     74111 non-null  float64\n",
      " 18  房型                       74111 non-null  int64  \n",
      " 19  邮编                       74111 non-null  int64  \n",
      " 20  价格                       59288 non-null  float64\n",
      " 21  容纳人数_freq                74111 non-null  float64\n",
      " 22  洗手间数量_freq               74111 non-null  float64\n",
      " 23  床的数量_freq                74111 non-null  float64\n",
      " 24  床的类型_freq                74111 non-null  float64\n",
      " 25  卧室数量_freq                74111 non-null  float64\n",
      " 26  取消条款_freq                74111 non-null  float64\n",
      " 27  所在城市_freq                74111 non-null  float64\n",
      " 28  清洁费_freq                 74111 non-null  float64\n",
      " 29  房主是否有个人资料图片_freq         74111 non-null  float64\n",
      " 30  房主回复率_freq               74111 non-null  float64\n",
      " 31  是否支持随即预订_freq            74111 non-null  float64\n",
      " 32  民宿周边_freq                74111 non-null  float64\n",
      " 33  房产类型_freq                74111 non-null  float64\n",
      " 34  房型_freq                  74111 non-null  float64\n",
      " 35  邮编_freq                  74111 non-null  float64\n",
      " 36  人均床数量                    74111 non-null  float64\n",
      " 37  人均卧室量                    74111 non-null  float64\n",
      " 38  卧室床均量                    74111 non-null  float64\n",
      " 39  经纬度平方根                   74111 non-null  float64\n",
      " 40  城市维度跨度                   74111 non-null  float64\n",
      " 41  城市经度跨度                   74111 non-null  float64\n",
      " 42  城市面积                     74111 non-null  float64\n",
      " 43  城市房密度                    74111 non-null  float64\n",
      " 44  timestamp_diff1          74111 non-null  int64  \n",
      " 45  timestamp_diff2          74111 non-null  int64  \n",
      " 46  timestamp_diff3          74111 non-null  int64  \n",
      " 47  房型_timestamp_diff1_max   74111 non-null  int64  \n",
      " 48  房型_timestamp_diff2_max   74111 non-null  int64  \n",
      " 49  房型_timestamp_diff3_max   74111 non-null  int64  \n",
      " 50  房型_timestamp_diff1_min   74111 non-null  int64  \n",
      " 51  房型_timestamp_diff2_min   74111 non-null  int64  \n",
      " 52  房型_timestamp_diff3_min   74111 non-null  int64  \n",
      " 53  房型_timestamp_diff1_mean  74111 non-null  float64\n",
      " 54  房型_timestamp_diff2_mean  74111 non-null  float64\n",
      " 55  房型_timestamp_diff3_mean  74111 non-null  float64\n",
      " 56  房型_timestamp_diff1_std   74111 non-null  float64\n",
      " 57  房型_timestamp_diff2_std   74111 non-null  float64\n",
      " 58  房型_timestamp_diff3_std   74111 non-null  float64\n",
      " 59  便利设施_tfidf_0             74111 non-null  float64\n",
      " 60  便利设施_tfidf_1             74111 non-null  float64\n",
      " 61  便利设施_tfidf_2             74111 non-null  float64\n",
      " 62  便利设施_tfidf_3             74111 non-null  float64\n",
      " 63  便利设施_tfidf_4             74111 non-null  float64\n",
      " 64  便利设施_tfidf_5             74111 non-null  float64\n",
      " 65  便利设施_tfidf_6             74111 non-null  float64\n",
      " 66  便利设施_tfidf_7             74111 non-null  float64\n",
      " 67  便利设施_tfidf_8             74111 non-null  float64\n",
      " 68  便利设施_tfidf_9             74111 non-null  float64\n",
      " 69  便利设施_tfidf_10            74111 non-null  float64\n",
      " 70  便利设施_tfidf_11            74111 non-null  float64\n",
      "dtypes: float64(48), int64(22), object(1)\n",
      "memory usage: 40.7+ MB\n"
     ]
    }
   ],
   "source": [
    "df_features.drop(['房主身份是否验证', '便利设施', '首次评论日期', '何时成为房主',\n",
    "                  '最近评论日期'], axis=1, inplace=True)\n",
    "df_features.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(59288, 71) (14823, 71)\n"
     ]
    }
   ],
   "source": [
    "df_test = df_features[df_features['价格'].isnull()].copy()\n",
    "df_train = df_features[df_features['价格'].notnull()].copy()\n",
    "\n",
    "print(df_train.shape, df_test.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "bad_feats = ['房型_timestamp_diff3_std',\n",
    " '房型_timestamp_diff3_mean',\n",
    " '房型_timestamp_diff2_min',\n",
    " '房型_timestamp_diff1_std',\n",
    " '房型_timestamp_diff1_mean']\n",
    "\n",
    "# bad_feats = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Fold_1 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[500]\ttrain's rmse: 4.12885\ttrain's l2: 17.0474\tvalid's rmse: 5.56874\tvalid's l2: 31.0108\n",
      "Early stopping, best iteration is:\n",
      "[552]\ttrain's rmse: 4.03646\ttrain's l2: 16.293\tvalid's rmse: 5.56289\tvalid's l2: 30.9458\n",
      "\n",
      "Fold_2 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "Early stopping, best iteration is:\n",
      "[363]\ttrain's rmse: 4.40172\ttrain's l2: 19.3752\tvalid's rmse: 5.52486\tvalid's l2: 30.5241\n",
      "\n",
      "Fold_3 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[500]\ttrain's rmse: 4.11544\ttrain's l2: 16.9368\tvalid's rmse: 5.41944\tvalid's l2: 29.3703\n",
      "Early stopping, best iteration is:\n",
      "[453]\ttrain's rmse: 4.20266\ttrain's l2: 17.6623\tvalid's rmse: 5.41659\tvalid's l2: 29.3395\n",
      "\n",
      "Fold_4 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "Early stopping, best iteration is:\n",
      "[430]\ttrain's rmse: 4.27284\ttrain's l2: 18.2571\tvalid's rmse: 5.34117\tvalid's l2: 28.5281\n",
      "\n",
      "Fold_5 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "Early stopping, best iteration is:\n",
      "[313]\ttrain's rmse: 4.50285\ttrain's l2: 20.2756\tvalid's rmse: 5.47131\tvalid's l2: 29.9352\n",
      "\n",
      "Fold_6 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "Early stopping, best iteration is:\n",
      "[399]\ttrain's rmse: 4.3246\ttrain's l2: 18.7021\tvalid's rmse: 5.48249\tvalid's l2: 30.0576\n",
      "\n",
      "Fold_7 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[500]\ttrain's rmse: 4.09911\ttrain's l2: 16.8027\tvalid's rmse: 5.50001\tvalid's l2: 30.2501\n",
      "Early stopping, best iteration is:\n",
      "[474]\ttrain's rmse: 4.15535\ttrain's l2: 17.2669\tvalid's rmse: 5.49478\tvalid's l2: 30.1926\n",
      "\n",
      "Fold_8 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "Early stopping, best iteration is:\n",
      "[404]\ttrain's rmse: 4.31569\ttrain's l2: 18.6252\tvalid's rmse: 5.37819\tvalid's l2: 28.925\n",
      "\n",
      "Fold_9 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[500]\ttrain's rmse: 4.13701\ttrain's l2: 17.1148\tvalid's rmse: 5.42885\tvalid's l2: 29.4724\n",
      "Early stopping, best iteration is:\n",
      "[450]\ttrain's rmse: 4.236\ttrain's l2: 17.9437\tvalid's rmse: 5.42641\tvalid's l2: 29.4459\n",
      "\n",
      "Fold_10 Training ================================\n",
      "\n",
      "[LightGBM] [Warning] feature_fraction is set=0.8, colsample_bytree=1.0 will be ignored. Current value: feature_fraction=0.8\n",
      "Training until validation scores don't improve for 50 rounds\n",
      "[500]\ttrain's rmse: 4.15565\ttrain's l2: 17.2694\tvalid's rmse: 5.21229\tvalid's l2: 27.168\n",
      "Early stopping, best iteration is:\n",
      "[540]\ttrain's rmse: 4.08837\ttrain's l2: 16.7148\tvalid's rmse: 5.20476\tvalid's l2: 27.0895\n"
     ]
    }
   ],
   "source": [
    "ycol = '价格'\n",
    "feature_names = list(\n",
    "    filter(lambda x: x not in [ycol, '数据ID'] + bad_feats, df_train.columns))\n",
    "\n",
    "model = lgb.LGBMRegressor(num_leaves=64,\n",
    "                          max_depth=6,\n",
    "                          learning_rate=0.1,\n",
    "                          n_estimators=10000,\n",
    "                          subsample=0.8,\n",
    "                          feature_fraction=0.8,\n",
    "                          reg_alpha=0.5,\n",
    "                          reg_lambda=0.5,\n",
    "                          random_state=2021,\n",
    "                          importance_type='gain',\n",
    "                          metric=None\n",
    "                          )\n",
    "\n",
    "\n",
    "oof = []\n",
    "prediction = df_test[['数据ID']]\n",
    "prediction[ycol] = 0\n",
    "df_importance_list = []\n",
    "\n",
    "kfold = KFold(n_splits=10, shuffle=True, random_state=2021)\n",
    "for fold_id, (trn_idx, val_idx) in enumerate(kfold.split(df_train[feature_names])):\n",
    "    X_train = df_train.iloc[trn_idx][feature_names]\n",
    "    Y_train = df_train.iloc[trn_idx][ycol]\n",
    "\n",
    "    X_val = df_train.iloc[val_idx][feature_names]\n",
    "    Y_val = df_train.iloc[val_idx][ycol]\n",
    "\n",
    "    print('\\nFold_{} Training ================================\\n'.format(fold_id+1))\n",
    "\n",
    "    lgb_model = model.fit(X_train,\n",
    "                          Y_train,\n",
    "                          eval_names=['train', 'valid'],\n",
    "                          eval_set=[(X_train, Y_train), (X_val, Y_val)],\n",
    "                          verbose=500,\n",
    "                          eval_metric='rmse',\n",
    "                          early_stopping_rounds=50)\n",
    "\n",
    "    pred_val = lgb_model.predict(\n",
    "        X_val, num_iteration=lgb_model.best_iteration_)\n",
    "    df_oof = df_train.iloc[val_idx][['数据ID', ycol]].copy()\n",
    "    df_oof['pred'] = pred_val\n",
    "    oof.append(df_oof)\n",
    "\n",
    "    pred_test = lgb_model.predict(\n",
    "        df_test[feature_names], num_iteration=lgb_model.best_iteration_)\n",
    "    prediction['价格'] += pred_test / kfold.n_splits\n",
    "\n",
    "    df_importance = pd.DataFrame({\n",
    "        'column': feature_names,\n",
    "        'importance': lgb_model.feature_importances_,\n",
    "    })\n",
    "    df_importance_list.append(df_importance)\n",
    "\n",
    "    del lgb_model, pred_val, pred_test, X_train, Y_train, X_val, Y_val\n",
    "    gc.collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>column</th>\n",
       "      <th>importance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>房型</td>\n",
       "      <td>9.227175e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>洗手间数量</td>\n",
       "      <td>2.136790e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>容纳人数</td>\n",
       "      <td>1.200462e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>房型_timestamp_diff1_max</td>\n",
       "      <td>1.128067e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>经度</td>\n",
       "      <td>1.030693e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>邮编</td>\n",
       "      <td>9.727778e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>卧室数量</td>\n",
       "      <td>9.231843e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>经纬度平方根</td>\n",
       "      <td>6.784083e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>维度</td>\n",
       "      <td>6.751959e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>洗手间数量_freq</td>\n",
       "      <td>6.713290e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>邮编_freq</td>\n",
       "      <td>5.017061e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>城市房密度</td>\n",
       "      <td>4.916253e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>timestamp_diff1</td>\n",
       "      <td>3.233038e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>timestamp_diff3</td>\n",
       "      <td>3.133611e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>房型_freq</td>\n",
       "      <td>3.075970e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>评论个数</td>\n",
       "      <td>2.803150e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>房主回复率</td>\n",
       "      <td>2.569475e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>民宿评分</td>\n",
       "      <td>2.371326e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>便利设施_tfidf_8</td>\n",
       "      <td>2.319501e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>人均床数量</td>\n",
       "      <td>2.082061e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>民宿周边_freq</td>\n",
       "      <td>2.079871e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>便利设施_tfidf_9</td>\n",
       "      <td>2.035433e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>便利设施_tfidf_1</td>\n",
       "      <td>1.958093e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>民宿周边</td>\n",
       "      <td>1.898073e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>便利设施_tfidf_11</td>\n",
       "      <td>1.784452e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>便利设施_tfidf_0</td>\n",
       "      <td>1.711496e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>timestamp_diff2</td>\n",
       "      <td>1.445040e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>卧室数量_freq</td>\n",
       "      <td>1.401537e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>房型_timestamp_diff2_mean</td>\n",
       "      <td>1.351810e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>便利设施_tfidf_7</td>\n",
       "      <td>1.328175e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>便利设施_tfidf_2</td>\n",
       "      <td>1.268449e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>便利设施_tfidf_10</td>\n",
       "      <td>1.261527e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>便利设施_tfidf_4</td>\n",
       "      <td>1.177373e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>便利设施_tfidf_5</td>\n",
       "      <td>1.153149e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>房产类型</td>\n",
       "      <td>1.110365e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>便利设施_tfidf_6</td>\n",
       "      <td>1.067831e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>便利设施_tfidf_3</td>\n",
       "      <td>1.030392e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>房产类型_freq</td>\n",
       "      <td>9.676819e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>人均卧室量</td>\n",
       "      <td>8.823106e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>所在城市</td>\n",
       "      <td>7.670116e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>城市维度跨度</td>\n",
       "      <td>5.845464e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>卧室床均量</td>\n",
       "      <td>5.474246e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>容纳人数_freq</td>\n",
       "      <td>5.257165e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>房主回复率_freq</td>\n",
       "      <td>4.115335e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>清洁费</td>\n",
       "      <td>4.115294e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>取消条款</td>\n",
       "      <td>3.333706e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>床的数量</td>\n",
       "      <td>2.296947e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>床的数量_freq</td>\n",
       "      <td>1.795530e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>所在城市_freq</td>\n",
       "      <td>1.665091e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>是否支持随即预订</td>\n",
       "      <td>1.586788e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>房型_timestamp_diff2_max</td>\n",
       "      <td>1.364061e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>清洁费_freq</td>\n",
       "      <td>1.361378e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>床的类型</td>\n",
       "      <td>1.306040e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>取消条款_freq</td>\n",
       "      <td>9.700180e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>床的类型_freq</td>\n",
       "      <td>6.250679e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>房主是否有个人资料图片</td>\n",
       "      <td>3.879201e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>是否支持随即预订_freq</td>\n",
       "      <td>3.202847e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>房型_timestamp_diff1_min</td>\n",
       "      <td>2.942894e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>城市经度跨度</td>\n",
       "      <td>1.886753e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>房主是否有个人资料图片_freq</td>\n",
       "      <td>5.945107e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>房型_timestamp_diff3_min</td>\n",
       "      <td>2.898389e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>城市面积</td>\n",
       "      <td>2.136918e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>房型_timestamp_diff2_std</td>\n",
       "      <td>1.914202e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>房型_timestamp_diff3_max</td>\n",
       "      <td>3.676140e+01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     column    importance\n",
       "0                        房型  9.227175e+06\n",
       "1                     洗手间数量  2.136790e+06\n",
       "2                      容纳人数  1.200462e+06\n",
       "3    房型_timestamp_diff1_max  1.128067e+06\n",
       "4                        经度  1.030693e+06\n",
       "5                        邮编  9.727778e+05\n",
       "6                      卧室数量  9.231843e+05\n",
       "7                    经纬度平方根  6.784083e+05\n",
       "8                        维度  6.751959e+05\n",
       "9                洗手间数量_freq  6.713290e+05\n",
       "10                  邮编_freq  5.017061e+05\n",
       "11                    城市房密度  4.916253e+05\n",
       "12          timestamp_diff1  3.233038e+05\n",
       "13          timestamp_diff3  3.133611e+05\n",
       "14                  房型_freq  3.075970e+05\n",
       "15                     评论个数  2.803150e+05\n",
       "16                    房主回复率  2.569475e+05\n",
       "17                     民宿评分  2.371326e+05\n",
       "18             便利设施_tfidf_8  2.319501e+05\n",
       "19                    人均床数量  2.082061e+05\n",
       "20                民宿周边_freq  2.079871e+05\n",
       "21             便利设施_tfidf_9  2.035433e+05\n",
       "22             便利设施_tfidf_1  1.958093e+05\n",
       "23                     民宿周边  1.898073e+05\n",
       "24            便利设施_tfidf_11  1.784452e+05\n",
       "25             便利设施_tfidf_0  1.711496e+05\n",
       "26          timestamp_diff2  1.445040e+05\n",
       "27                卧室数量_freq  1.401537e+05\n",
       "28  房型_timestamp_diff2_mean  1.351810e+05\n",
       "29             便利设施_tfidf_7  1.328175e+05\n",
       "30             便利设施_tfidf_2  1.268449e+05\n",
       "31            便利设施_tfidf_10  1.261527e+05\n",
       "32             便利设施_tfidf_4  1.177373e+05\n",
       "33             便利设施_tfidf_5  1.153149e+05\n",
       "34                     房产类型  1.110365e+05\n",
       "35             便利设施_tfidf_6  1.067831e+05\n",
       "36             便利设施_tfidf_3  1.030392e+05\n",
       "37                房产类型_freq  9.676819e+04\n",
       "38                    人均卧室量  8.823106e+04\n",
       "39                     所在城市  7.670116e+04\n",
       "40                   城市维度跨度  5.845464e+04\n",
       "41                    卧室床均量  5.474246e+04\n",
       "42                容纳人数_freq  5.257165e+04\n",
       "43               房主回复率_freq  4.115335e+04\n",
       "44                      清洁费  4.115294e+04\n",
       "45                     取消条款  3.333706e+04\n",
       "46                     床的数量  2.296947e+04\n",
       "47                床的数量_freq  1.795530e+04\n",
       "48                所在城市_freq  1.665091e+04\n",
       "49                 是否支持随即预订  1.586788e+04\n",
       "50   房型_timestamp_diff2_max  1.364061e+04\n",
       "51                 清洁费_freq  1.361378e+04\n",
       "52                     床的类型  1.306040e+04\n",
       "53                取消条款_freq  9.700180e+03\n",
       "54                床的类型_freq  6.250679e+03\n",
       "55              房主是否有个人资料图片  3.879201e+03\n",
       "56            是否支持随即预订_freq  3.202847e+03\n",
       "57   房型_timestamp_diff1_min  2.942894e+03\n",
       "58                   城市经度跨度  1.886753e+03\n",
       "59         房主是否有个人资料图片_freq  5.945107e+02\n",
       "60   房型_timestamp_diff3_min  2.898389e+02\n",
       "61                     城市面积  2.136918e+02\n",
       "62   房型_timestamp_diff2_std  1.914202e+02\n",
       "63   房型_timestamp_diff3_max  3.676140e+01"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_importance = pd.concat(df_importance_list)\n",
    "df_importance = df_importance.groupby(['column'])['importance'].agg(\n",
    "    'mean').sort_values(ascending=False).reset_index()\n",
    "df_importance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_importance[df_importance.importance == 0]['column'].values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "rmse: 5.43123935804306\n"
     ]
    }
   ],
   "source": [
    "df_oof = pd.concat(oof)\n",
    "rmse = mean_squared_error(df_oof[ycol], df_oof['pred'], squared=False)\n",
    "print('rmse:', rmse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "sub = prediction.copy(deep=True)\n",
    "sub.to_csv(f'sub_{rmse}.csv', index=False, encoding='utf-8')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
